# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from xlwt import *
import xlrd
from xlutils.copy import copy
import os
import sys

reload(sys)
sys.setdefaultencoding('utf8')

"""
author：zhangqing
function:cv 行业
ps:只对cv行业，不对国标、证监会等
"""

def searchcvs1Company():
    db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw",
                         "cms_plus", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    sql = "SELECT COUNT(a.ext1) a FROM tbl_company a WHERE a.`status`='1' ORDER BY a.ext1 DESC "
    cursor.execute(sql)
    resultsCount = cursor.fetchone()
    pageCount = resultsCount["a"]/50
    for index in range(pageCount):
    # for index in range(1,4):
    #上次跑到
        limitStart = index * 50
        print("limitStart====================================================",limitStart)

        #通过cms_plus.tbl_company的ext1字段 和datacenter_online_20180609.dc_enterprise的id字段 做关联找到industry_type(cvs1.0的行业id)
        company1Sql = "SELECT a.ext1 AS enterprisId,a.full_name, '' AS beizhu " \
                      "FROM tbl_company a WHERE a.`status`='1' ORDER BY a.ext1 DESC limit %d,50 "%limitStart

        # company1Sql = "SELECT a.id,a.ext1 AS enterprisId,a.full_name, '' AS beizhu " \
        #               "FROM tbl_company a WHERE a.`status`='1' AND a.full_name='玛雅科技（广州）有限公司'"

        cursor.execute(company1Sql)
        company1Results = cursor.fetchall()
        fields = cursor.description
        for company1Index in range(len(company1Results)):
            companyId = company1Results[company1Index].get(u"id")
            #enterpriseId 获取此企业在1.0中对应的行业名称
            industry_name1 = getcvs1Industry(company1Results[company1Index], fields,companyId)
            if industry_name1=='isNotEnterprise' or industry_name1=='tagAndIndustryEnd' or industry_name1=='isNotEvent':
                break
            #1.0中的企业
            companyName1 = company1Results[company1Index].get(u"full_name")
            #根据此企业在1.0中对应的行业名称返回2.0中此企业应该对应的行业分类名字
            industryName2Sql = "SELECT cvs2_name FROM industry_v1_v2 WHERE cvs1_name = '%s' "%industry_name1
            cursor.execute(industryName2Sql)
            industryName2Results = cursor.fetchall()
            cvs2IndustrName = industryName2Results[0].get(u"cvs2_name")
            #找到了企业，和此企业应该在2.0中对应的行业后，看2.0中的结果表中的数据是否与此一致
            compareCvs1And2Industry(company1Results[company1Index],fields,companyName1, cvs2IndustrName)

    db.close()

def compareCvs1And2Industry(company1Results,fields,companyName1, cvs2IndustrName):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "cms_plus", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    viewCompany2Sql = "SELECT c.full_name, c.industryLevel1Name, d.`name` AS industryLevel2Name " \
                     "FROM " \
                     "( " \
                     "SELECT a.id, a.full_name, b.`code` AS industry2Code, b.`name` AS industryLevel1Name, " \
                     "b.`level` AS industryLevel, a.industry_cv_2 " \
                     "FROM view_company a, tbl_industry_cv b " \
                     "WHERE a.industry_cv_1 = b.`code` AND b.`level` = 1 AND a.full_name  = '%s' " \
                     ") c, tbl_industry_cv d " \
                     "WHERE c.industry_cv_2 = d.`code` AND d.`level` = 2 AND c.full_name  = '%s' "%(companyName1,companyName1)
    cursor.execute(viewCompany2Sql)
    viewCompany2Results = cursor.fetchall()
    if len(viewCompany2Results) == 0:
        beizhuStr1=u'此企业在cvs2中view_company不存在，企业名称为：' +companyName1
        writeDataOne(company1Results, fields, beizhuStr1)
    else:
        for viewCompany2Index in range(len(viewCompany2Results)):
            industryLevel1Name = viewCompany2Results[viewCompany2Index].get(u"industryLevel1Name")
            industryLevel2Name = viewCompany2Results[viewCompany2Index].get(u"industryLevel2Name")
            industry2Name = industryLevel1Name+"-"+industryLevel2Name
            isIndustryName = (cvs2IndustrName == industry2Name)
            if isIndustryName != True:
                beizhuStr1=u'此企业在cvs2中view_company行业不一致，cvs1中为：' +cvs2IndustrName+"|| cvs2中为"+industry2Name
                writeDataOne(company1Results, fields, beizhuStr1)
    db.close()

def getcvs1Industry(company1Results, fields,companyId):
    db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    # for company1Index in range(len(company1Results)):
    enterprisId1 = company1Results.get(u"enterprisId")
    enterprise1Sql = "SELECT a.industry_type,a.name FROM dc_enterprise a WHERE a.id = '%s' "%enterprisId1
    cursor.execute(enterprise1Sql)
    enterprise1Results = cursor.fetchall()
    #此处判断大于0，因为tbl_company 中ext1不仅存了企业enterprise_id，还存了lp_id,fund_id,agency，能匹配上的才是企业
    if len(enterprise1Results) > 0:
        industry_type1 = enterprise1Results[0].get(u"industry_type")
        enterpriseName1 = enterprise1Results[0].get(u"name")
        if enterpriseName1 != None:
            #dc_enterprise中找到此企业的行业字典后，去查找（企名片和itjuzi标签中看此行业是否已经有了标签和行业的对应关系，如果有，则取此行业，没有才取字典中对应的行业）
            industry_name1 = getcvs1and2IndustryName(company1Results,fields,enterpriseName1,industry_type1,companyId)
            return industry_name1
        else:
            return "isNotEnterprise"
    else:
        return "isNotEnterprise"
    db.close()

def getcvs1and2IndustryName(company1Results,fields,enterpriseName1,industry_type1,companyId):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "test", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    args = '%-%'
    IndustryNameTestTagQuanSql = "SELECT d.full_name,COUNT(d.full_name) sort " \
                             "FROM " \
                             "(" \
                             "SELECT b.industry_id,a.company_name FROM dc_company_tag_all a " \
                             "LEFT JOIN tbl_industry_tags_rel b " \
                             "ON a.tag_id=b.tags_id WHERE a.company_name = '%s'" \
                             ") c " \
                             "LEFT JOIN tbl_industry_cv d ON c.industry_id = d.id " \
                             "WHERE c.company_name = '%s' AND d.full_name LIKE'%s' " \
                             "GROUP BY d.full_name ORDER BY sort DESC LIMIT 1 "%(enterpriseName1,enterpriseName1,args)

    industryNameTestQuanSql = "SELECT full_name FROM dict_industry WHERE id = '%s' "%industry_type1

    cursor.execute(IndustryNameTestTagQuanSql)
    industryNameTestTagQuanResults = cursor.fetchall()

    cursor.execute(industryNameTestQuanSql)
    industryNameTestQuanResults = cursor.fetchall()
    # print(IndustryNameTestQuanSql)
    if len(industryNameTestTagQuanResults)>0:
        industry_name1 = industryNameTestTagQuanResults[0].get(u"full_name")
        companyName1 = company1Results.get(u"full_name")

        event2Sql = "SELECT id FROM tbl_event WHERE company_id = '%s' "%companyId
        cursor.execute(event2Sql)
        event2Results = cursor.fetchall()
        if len(event2Results)==0:
            return 'isNotEvent'

        #如果是标签和行业已经有了对应关系，则直接拿此对应关系中的行业名称与 此企业在2.0中已经打上的行业对比，看2.0中的结果表中的数据是否与此一致
        #此处industry_name1就是在2.0中此企业应该打上的行业，因为这是行业和标签有关联关系应该打上的标签，就是最终2.0应该打上的最终标签
        compareCvs1And2Industry(company1Results,fields,companyName1, industry_name1)
        return 'tagAndIndustryEnd'

    else:
        industry_name1 = industryNameTestQuanResults[0].get(u"full_name")
    return industry_name1

    db.close()

def writeDataOne(orgResults1, fields, beizhuStr1):
     orgResults1.update(beizhu=beizhuStr1)
     writeExcel(orgResults1, fields)

def writeExcel(results,fields):

    fileName = 'dataIndustryResult.xlsx'
    p1 = os.path.exists(fileName)

    if p1:
        f = xlrd.open_workbook(fileName, formatting_info=True)
        sheet = f.sheet_by_index(0)
        rowNum = sheet.nrows
        newbook =copy(f)
        newsheet = newbook.get_sheet(0)

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print rowNum,j,x
            newsheet.write(rowNum,j,x)

        newbook.save(fileName)

    else:

        workbook  = Workbook(encoding = 'utf-8')
        table = workbook .add_sheet('data')

        for field in range(0,len(fields)):
            table.write(0,field,fields[field][0])

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print 1,j,x
            table.write(1,j,x)

        workbook .save(fileName)
